package exttable.formgenerator;


import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;
import dbutils.DbUtils;
import dbutils.StoredQueries;
import dbutils.StringQueries;
import exttable.SGTTestParametrs;
import exttable.dbchecking.RunDBChecking;
import exttable.dbclasses.*;
import exttable.testselector.TestUtils;
import exttable.testselector.VolunteerSelectorView;
import exttable.threads.ThreadFactory;
import utils.Localisation;

import javax.swing.*;
import java.awt.*;
import java.io.*;
import java.net.URL;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.util.List;
import java.util.zip.Deflater;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 20.12.2010
 * Time: 13:54:23
 * To change this template use File | Settings | File Templates.
 */
public class StaticMethods {

    public static String getStringForInFromString(List<Object> phaseListToReturn) {
        String toReturn = "";
        for (Object s : phaseListToReturn) {
            Phase phase = (Phase) s;
            toReturn = toReturn + "'" + phase.getPhaseId() + "', ";

        }
        toReturn = toReturn.substring(0, toReturn.length() - 2);

        return toReturn;
    }

    public static String getStringForInFromGroup(List<Object> groupListToReturn) {
        String toReturn = "";
        for (Object s : groupListToReturn) {
            Group group = (Group) s;
            toReturn = toReturn + "'" + group.getGroupId() + "', ";

        }
        toReturn = toReturn.substring(0, toReturn.length() - 2);

        return toReturn;
    }

    public static String getStringForInFromProbationerList(List<Object> probationerListToReturn) {
        String toReturn = "";
        for (Object s : probationerListToReturn) {
            Probationer probationer = (Probationer) s;
            toReturn = toReturn + "'" + probationer.getProbationer_id() + "', ";

        }
        toReturn = toReturn.substring(0, toReturn.length() - 2);

        return toReturn;
    }

    public static boolean convertIntToBoolean(int toConvert) {
        return toConvert == 1;
    }

    public static int convertBooleanToInt(boolean toConvert) {
        if (toConvert == true) {
            return 1;
        }
        return 0;
    }

    public static List<Volunteer> getVolunteerList() {
        List<Volunteer> volunteerList = new ArrayList<Volunteer>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("select * from volunteers order by volunteer_code");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Volunteer volunteer = new Volunteer();
                volunteer.setVolunteerId(rs.getInt("volunteer_id"));
                volunteer.setVolunteerCode(rs.getString("volunteer_code"));
                volunteer.setVolunteerDateBirth(rs.getDate("volunteer_date_birth"));
                volunteer.setNationalityId(rs.getInt("nationality_id"));
                volunteer.setSexId(rs.getInt("sex_id"));
                volunteer.setVolunteerLastname(rs.getString("volunteer_lastname"));
                volunteer.setVolunteerFirstname(rs.getString("volunteer_firstname"));
                volunteer.setVolunteerPatronymicName(rs.getString("volunteer_patronymic_name"));
                volunteer.setVolunteerIdentityCardNumber(rs.getString("volunteer_identity_card_number"));
                volunteer.setVolunteerIdentityCardDateIssue(rs.getDate("volunteer_identity_card_date_issue"));
                volunteer.setPlaceOfLiving(rs.getString("place_of_living"));
                volunteer.setPlaceOfEmployment(rs.getString("place_of_employment"));
                volunteer.setMaritalStatusId(rs.getInt("marital_status_id"));
                volunteer.setPhoneNumber(rs.getString("phone_number"));
                volunteer.setMobilePhoneNumber(rs.getString("mobile_phone_number"));
                volunteer.setEmail(rs.getString("email"));
                volunteer.setOccupationId(rs.getInt("occupation_id"));
                volunteer.setAlcoholic(convertIntToBoolean(rs.getInt("is_alcoholic")));
                volunteer.setNarcomaniac(convertIntToBoolean(rs.getInt("is_narcomaniac")));
                volunteer.setSmokingNow(convertIntToBoolean(rs.getInt("smoking_now")));
                volunteer.setHowLongYearsSmoking(rs.getInt("how_long_years_smoking"));
                volunteer.setTypeSmokingId(rs.getInt("type_smoking_id"));
                volunteer.setHowManyCigarretesId(rs.getInt("how_many_cigarretes_id"));
                volunteer.setDrinkBeer(convertIntToBoolean(rs.getInt("drink_beer")));
                volunteer.setHowMuchBeerWeekId(rs.getInt("how_much_beer_week_id"));
                volunteer.setDrinkWine(convertIntToBoolean(rs.getInt("drink_wine")));
                volunteer.setHowMuchWineWeekId(rs.getInt("how_much_wine_week_id"));
                volunteer.setDrinkOtherAlcoholicDrinks(convertIntToBoolean(rs.getInt("drink_other_alcoholic_drinks")));
                volunteer.setHowMuchDrinkOtherAlcoholicDrinksWeekId(rs.getInt("how_much_drink_other_alcoholic_drinks_week_id"));
                volunteer.setHeight(rs.getDouble("height"));
                volunteer.setIndexBodyWeight(rs.getDouble("index_body_weight"));
                volunteer.setMaxArterialPressure(rs.getInt("max_arterial_pressure"));
                volunteer.setMinArterialPressure(rs.getInt("min_arterial_pressure"));
                volunteerList.add(volunteer);
            }

        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return volunteerList;
    }


    public static List<Probationer> getProbationerList() {
        List<Probationer> toReturn = new ArrayList<Probationer>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("select * from probationers order by probationer_code");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Probationer probationer = new Probationer();
                probationer.setProbationer_id(Integer.parseInt(rs.getString("probationer_id")));
                probationer.setProbationer_code(rs.getString("probationer_code"));
                probationer.setGroup_id(Integer.parseInt(rs.getString("group_id")));
//                probationer.setVisitList(SelectGroupAndProbationerForm.getVisitList(probationer.getProbationer_id(), conn));
                toReturn.add(probationer);
            }


            conn.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return toReturn;
    }


    public static void insertValuesToDB(final String tableName, final Vector<String> nameOfColumnForInsert, final Vector<Object> valueOfColumnForInsert, final JFrame frame, final int insertBySystem) {

        if (MessageFactory.isToSave()) {
            if (tableName.equals("units")) {
                nameOfColumnForInsert.add(0, "combination_key");
                if (valueOfColumnForInsert.get(1).equals("EMPTY")) {
                    valueOfColumnForInsert.add(0, valueOfColumnForInsert.get(0));
                } else {
                    valueOfColumnForInsert.add(0, valueOfColumnForInsert.get(0) + "_" + valueOfColumnForInsert.get(1));
                }
            }
            if (insertBySystem == 1) {
                nameOfColumnForInsert.add(TestUtils.universalColumnsArray.get(1));
                nameOfColumnForInsert.add(TestUtils.universalColumnsArray.get(2));
                valueOfColumnForInsert.add(SGTTestParametrs.getInstance().getUserName());
                JFormattedTextField dateField = new JFormattedTextField(new SimpleDateFormat("dd-MM-yyyy"));
                dateField.setValue(new Date());
                valueOfColumnForInsert.add(returnFormatedData(dateField.getText()));
            }
            String columns;
            String values;
            StringBuffer buff = new StringBuffer();
            try {
                for (String s : nameOfColumnForInsert) {
                    if (buff.length() == 0) {
                        buff.append(s);
                    } else {
                        buff.append("," + s);
                    }

                }
                columns = buff.toString();
                buff.setLength(0);
                for (Object obj : valueOfColumnForInsert) {
                    if (buff.length() == 0) {
                        buff.append("'").append(obj).append("'");
                    } else {
                        buff.append("," + "'").append(obj).append("'");
                    }
                }
                values = buff.toString();
                String query = "insert into " + tableName + "(" + columns + ") values(" + values + ")";
                Connection connGeneral = DbUtils.getDBConnectionGeneral();
                PreparedStatement psGeneral = connGeneral.prepareStatement(query);

                //      ps.execute();
                if (psGeneral.executeUpdate() > 0) {
                    if (DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                        Connection connSecondary = DbUtils.getDBConnectionSecondary();
                        PreparedStatement psSecondary = connSecondary.prepareStatement(query);
                        psSecondary.executeUpdate();
                        connSecondary.close();
                    }
                    JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded"), "", JOptionPane.INFORMATION_MESSAGE);
                    StaticMethods.getFrameByName("Form to insert").dispose();
                    frame.dispose();
                    if (ViewDataBaseTableModel.frame != null) {
                        ViewDataBaseTableModel.frame.dispose();
                        new ViewDataBaseTableModel(tableName);
                    }
                }
                connGeneral.close();
            } catch (MySQLIntegrityConstraintViolationException excep) {


                JOptionPane.showMessageDialog(null, ComponentsToPanel.getDuplicateMessage(excep.getMessage()), "error",
                        JOptionPane.ERROR_MESSAGE);
                excep.printStackTrace();
                frame.dispose();
            } catch (Exception excep) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("error.applicationerror"), "error",
                        JOptionPane.ERROR_MESSAGE);
                excep.printStackTrace();
                frame.dispose();
            }
        }
    }

    public static void updateRowInDB(final String tableName, final Vector<String> nameOfColumnForInsert, final Vector<Object> valueOfColumnForInsert, final JFrame frame, final int insertBySystem, final Object obj) {
        if (MessageFactory.isToSave()) {
            if (tableName.equals("units")) {
                nameOfColumnForInsert.add(0, "combination_key");
                if (valueOfColumnForInsert.get(1).equals("EMPTY")) {
                    valueOfColumnForInsert.add(0, valueOfColumnForInsert.get(0));
                } else {
                    valueOfColumnForInsert.add(0, valueOfColumnForInsert.get(0) + "_" + valueOfColumnForInsert.get(1));
                }
            }
            if (insertBySystem == 1) {
                nameOfColumnForInsert.add(TestUtils.universalColumnsArray.get(3));
                nameOfColumnForInsert.add(TestUtils.universalColumnsArray.get(4));

                valueOfColumnForInsert.add(SGTTestParametrs.getInstance().getUserName());
                JFormattedTextField dateField = new JFormattedTextField(new SimpleDateFormat("dd-MM-yyyy"));
                dateField.setValue(new Date());
                valueOfColumnForInsert.add(returnFormatedData(dateField.getText()));
            }

            String update;
            StringBuffer buff = new StringBuffer();

            try {
                for (int i = 0; i < nameOfColumnForInsert.size(); i++) {
                    if (buff.length() == 0) {
                        buff.append(nameOfColumnForInsert.get(i) + "='" + valueOfColumnForInsert.get(i) + "'");
                    } else {
                        buff.append("," + nameOfColumnForInsert.get(i) + "='" + valueOfColumnForInsert.get(i) + "'");
                    }
                }
                update = buff.toString();
//                System.out.println("update "+update);
                String updateQuery = "UPDATE " + tableName + " SET " + update + " WHERE " + ((DbTable) obj).getKeyFieldName() + "=" + ((DbTable) obj).getKeyFieldValue();

                Map<String, Object> mapNewValues = StaticMethods.joinVectorsToMap(nameOfColumnForInsert, valueOfColumnForInsert);
                /*
                LinkedList<String> columnNames = StaticMethods.getColumnNamesOfTable("fs1",tableName);
                Vector<Object> oldValues = new Vector<Object>();
                Connection conn = DbUtils.getDBConnectionGeneral();
                PreparedStatement ps = conn.prepareStatement("select * from " + tableName + " WHERE volunteer_id=" + ((Volunteer) obj).getVolunteerId());
                ResultSet rs = ps.executeQuery();
                ResultSetMetaData md = rs.getMetaData();
                while (rs.next()) {
                    for (int i = 0; i < md.getColumnCount(); i++) {
                        oldValues.add(rs.getString(i + 1));
                    }
                }
                Map<String, Object> mapOLdValues = StaticMethods.joinVectorsToMap(columnNames, oldValues);
*/
                Map<String, String> mapOLdValues = ((DbTable) obj).getMapValuesFields();
                List<String> listQueriesForHistory = new ArrayList<String>();
//                System.out.println("columnNames " + columnNames.size());
//                System.out.println("oldValues " + oldValues.size());
//                System.out.println("nameOfColumnForInsert " + nameOfColumnForInsert.size());
//                System.out.println("valueOfColumnForInsert " + valueOfColumnForInsert.size());
//                System.out.println("mapOLdValues *** "+mapOLdValues.get("modified_date").toString());
//                System.out.println("mapNewValues *** "+mapNewValues.get("modified_date").toString());
//                System.out.println("mapOLdValues *** "+mapOLdValues.get("modified_user"));
//                System.out.println("mapNewValues *** "+mapNewValues.get("modified_user").toString());
                for (Map.Entry<String, Object> res : mapNewValues.entrySet()) {
                    if (res.getKey().equals("modified_user") || res.getKey().equals("modified_date")) {
                        continue;
                    } else {
                        if (!res.getValue().toString().equals(mapOLdValues.get(res.getKey()).toString())) {

                            StringBuffer buffHistory = new StringBuffer();
                            buffHistory.append("'" + tableName + "','" +
                                    ((DbTable) obj).getKeyFieldValue() + "','" +
                                    res.getKey() + "','" + mapOLdValues.get(res.getKey()).toString() + "','" +
                                    res.getValue().toString() + "','" + mapNewValues.get("modified_date") + "','" +
                                    mapNewValues.get("modified_user") + "'");
                            String queryHistory = "INSERT INTO table_values_history VALUES(NULL," + buffHistory.toString() + ")";
                            listQueriesForHistory.add(queryHistory);

                        }

                    }


//                    System.out.println("new "+res.getKey()+" ="+res.getValue()+", old = "+mapOLdValues.get(res.getKey()));
                }
                Connection connGeneral = DbUtils.getDBConnectionGeneral();
                PreparedStatement psGeneralUpdate = connGeneral.prepareStatement(updateQuery);

                Connection connSecondary = null;
                PreparedStatement psSecondaryUpdate = null;
                if (psGeneralUpdate.executeUpdate() > 0) {
                    if (DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                        connSecondary = DbUtils.getDBConnectionSecondary();
                        psSecondaryUpdate = connSecondary.prepareStatement(updateQuery);
                        psSecondaryUpdate.executeUpdate();
                    }
                    JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded"), "", JOptionPane.INFORMATION_MESSAGE);
                    frame.dispose();
                    if (ViewDataBaseTableModel.frame != null) {
                        ViewDataBaseTableModel.frame.dispose();
                        new ViewDataBaseTableModel(tableName);
                    }
                    for (String query : listQueriesForHistory) {
                        PreparedStatement psGeneralHistory = connGeneral.prepareStatement(query);
                        PreparedStatement psSecondaryHistory = connSecondary.prepareStatement(query);
                        if (psGeneralHistory.executeUpdate() < 1) {
                            JOptionPane.showMessageDialog(null, Localisation.getMessage("error.applicationerror"), "error",
                                    JOptionPane.ERROR_MESSAGE);
                        } else {
                            if (DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                                psSecondaryHistory.executeUpdate();
                            }
                            JOptionPane.showMessageDialog(null, Localisation.getMessage("info.changingDataSaved"), "", JOptionPane.INFORMATION_MESSAGE);
                        }
                    }
                    if (tableName.equals("probationers")) {
                        String updateProbationersGroups = "UPDATE probationers_groups SET excluded=" +
                                mapNewValues.get("excluded") +
                                " WHERE probationer_code='" + mapNewValues.get("probationer_code") + "';";
                        PreparedStatement psGeneralProbationersGroups = connGeneral.prepareStatement(updateProbationersGroups);
                        psGeneralProbationersGroups.executeUpdate();
                        if (DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                            PreparedStatement psSecondaryProbationersGroups = connSecondary.prepareStatement(updateProbationersGroups);
                            psSecondaryProbationersGroups.executeUpdate();
                        }

                    }


                }
                connGeneral.close();
                connSecondary.close();

            } catch (Exception excep) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("error.applicationerror"), "error",
                        JOptionPane.ERROR_MESSAGE);
                excep.printStackTrace();
                frame.dispose();
            }


        }

    }

    public static Map<String, Object> joinVectorsToMap(Vector<String> vecKey, Vector<Object> vecValue) {
        Map<String, Object> toReturn = new HashMap<String, Object>();
        //if (vecKey.size() == vecValue.size()) {
        for (int i = 0; i < vecKey.size(); i++) {
            toReturn.put(vecKey.get(i), vecValue.get(i));
        }

        //}
        return toReturn;
    }

    public static String joinPhaseListToString(List<Phase> objectList) {
        String toReturn = "";
        for (int i = 0; i < objectList.size(); i++) {
            if (i == 0) {
                toReturn = "'" + objectList.get(i).getPhaseId() + "'";
            } else {
                toReturn = toReturn + ",'" + objectList.get(i).getPhaseId() + "'";
            }
        }
        return toReturn;
    }

    public static String joinStringListToString(List<String> stringList) {
        String toReturn = "";
        for (int i = 0; i < stringList.size(); i++) {
            if (i == 0) {
                toReturn = "'" + stringList.get(i)+ "'";
            } else {
                toReturn = toReturn + ",'" + stringList.get(i)+ "'";
            }
        }
        return toReturn;
    }

    public static LinkedList<String> getColumnNamesOfTable(String dataBaseName, String tableName) {
        LinkedList<String> toReturn = new LinkedList<String>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT COLUMN_NAME FROM information_schema.COLUMNS " +
                    "WHERE TABLE_SCHEMA = ?" +
                    " AND TABLE_NAME =?" + "" +
                    " AND COLUMN_NAME" +
                    " NOT IN('test_id','user','insert_datetime','modified_date','modified_user','modified_user','exceed_max_min','version_id')");
            ps.setString(1, dataBaseName);
            ps.setString(2, tableName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn.add(rs.getString(1));
            }
            conn.close();

        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return toReturn;
    }

    public static Map<String, DbTableField> getFieldMapOfTable(String dataBaseName, String tableName) {
        Map<String, DbTableField> toReturn = new HashMap<String, DbTableField>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            String query = "SELECT \n" +
                    "COLUMN_NAME,\n" +
                    "t1.parameter_id,\n" +
                    "t1.analys_param,\n" +
                    "t1.unit,\n" +
                    "t1.analys_table,\n" +
                    "t1.name_ru,\n" +
                    "t1.name_en,\n" +
                    "t1.name_kk\n" +
                    "FROM information_schema.COLUMNS\n" +
                    "LEFT OUTER JOIN\n" +
                    "(\n" +
                    "SELECT \n" +
                    "parameter_id,\n" +
                    "analys_param,\n" +
                    "unit,\n" +
                    "analys_table,\n" +
                    "name_ru,\n" +
                    "name_en,\n" +
                    "name_kk\n" +
                    "FROM analys_translation\n" +
                    "WHERE analys_table in(select analysis_instr_invest_list_id \n" +
                    "FROM dic_analysis_instr_invest_list\n" +
                    "WHERE analysis_name=?)\n" +
                    ")AS t1\n" +
                    "ON(information_schema.COLUMNS.COLUMN_NAME=t1.analys_param) \n" +
                    "WHERE TABLE_SCHEMA = ?\n" +
                    "AND TABLE_NAME = ? \n" +
                    "AND COLUMN_NAME\n" +
                    "NOT IN('test_id',\n" +
                    "'user','insert_datetime',\n" +
                    "'modified_date',\n" +
                    "'modified_user',\n" +
                    "'modified_user',\n" +
                    "'exceed_max_min',\n" +
                    "'version_id')";
            PreparedStatement ps = conn.prepareStatement(query);
            ps.setString(1, tableName);
            ps.setString(2, dataBaseName);
            ps.setString(3, tableName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DbTableField dbTableField = new DbTableField();
                dbTableField.setFieldName(rs.getString("COLUMN_NAME"));
                dbTableField.setUnit(rs.getString("unit"));
                dbTableField.setNameRu(rs.getString("name_ru"));
                dbTableField.setNameEn(rs.getString("name_en"));
                dbTableField.setNameKz(rs.getString("name_kk"));

                toReturn.put(rs.getString("COLUMN_NAME"), dbTableField);
            }
            conn.close();

        } catch (Exception exp) {
            exp.printStackTrace();
        }

        return toReturn;
    }

    public static String getIdField(String dataBaseName, String tableName) {
        /*
        This method return name of field that is auto_increment from special table
         */
        String toReturn = "";
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT COLUMN_NAME FROM information_schema.COLUMNS " +
                    "WHERE COLUMNS.TABLE_SCHEMA = '" + dataBaseName + "'" +
                    "AND COLUMNS.TABLE_NAME ='" + tableName +
                    "'AND COLUMNS.EXTRA='auto_increment'");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn = rs.getString(1);
            }
            conn.close();
            ps.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return toReturn;
    }

    ;

    public static Map<String, String> getMapValuesFields(DbTable dbTable) {
        Map<String, String> mapValuesFields = new HashMap<String, String>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM " + dbTable.getTableName() +
                    " WHERE " + dbTable.getKeyFieldName() + "='" + dbTable.getKeyFieldValue() + "'");
            System.out.println(ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                    mapValuesFields.put(rsmd.getColumnName(i), rs.getString(i));
//                    System.out.println("key " + rsmd.getColumnName(i)+" val "+rs.getString(i));
                }

            }
            conn.close();
            ps.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return mapValuesFields;
    }


    public static String returnFormatedData(String dataString) {
        DateFormat dateFormat1 = new SimpleDateFormat("dd-MM-yyyy", Locale.US);
        DateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
        Date date;
        String newDataString = null;
        try {
            date = dateFormat1.parse(dataString);
            newDataString = dateFormat2.format(date);
        } catch (Exception e2) {
            e2.printStackTrace();

        }
        return newDataString;
    }

    public static String returnNotFormatedData(String dataString) {
        DateFormat dateFormat1 = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
        DateFormat dateFormat2 = new SimpleDateFormat("dd-MM-yyyy", Locale.US);
        Date date;
        String newDataString = null;
        try {
            date = dateFormat1.parse(dataString);
            newDataString = dateFormat2.format(date);
        } catch (Exception e2) {
            e2.printStackTrace();

        }
        return newDataString;
    }

    static String returnFormattedDataFromDate(Date date) {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd", Locale.US);
        return dateFormat.format(date);
    }
    /*
   This method return List<Group> of all groups
    */

    public static List<Group> getGroupList() throws ClassNotFoundException, SQLException {
        List<Group> groupsList = new ArrayList<Group>();
        Connection conn = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = conn.prepareStatement("select * from groups ");
        try {
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Group group = new Group();
                group.setGroupId(Integer.parseInt(rs.getString("group_id")));
                group.setGroupCode(rs.getString("group_code"));
                groupsList.add(group);
            }
            rs.close();
            return groupsList;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new SQLException("sql error");
        } catch (NumberFormatException e) {
            e.printStackTrace();
            throw new SQLException("not a number");
        } finally {
            if (ps != null) {
                ps.close();
            }
            conn.close();

        }
    }

    public static List<Group> getAnalysGroupList() {
        /*
        This method return List<Group> for only medical tests(ANALYS).
         */
        List<Group> groupsList = new ArrayList<Group>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps;
            ps = conn.prepareStatement("SELECT groups_test_type.group_id, " +
                    "groups.group_code " +
                    "FROM groups_test_type " +
                    "INNER JOIN groups " +
                    "ON(groups_test_type.group_id=groups.group_id) " +
                    "WHERE groups_schema_visits_id IN( " +
                    "SELECT groups_schema_visits_id FROM groups_schema_visits " +
                    "WHERE dic_test_type_id='ANALYS' " +
                    ") " +
                    "ORDER BY groups_test_type.group_id  ");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Group group = new Group();
                group.setGroupId(Integer.parseInt(rs.getString("group_id")));
                group.setGroupCode(rs.getString("group_code"));
//                group.setProbationerList(SelectGroupAndProbationerForm.getProbationerListByGroup(group.getGroupId(), conn));
                groupsList.add(group);
            }
            conn.close();
            ps.close();
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return groupsList;
    }

    public static String getGroupName(int groupId) {
        String toReturn = "";
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT group_code FROM groups WHERE group_id=" + groupId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                toReturn = rs.getString(1);
            }
            conn.close();
            ps.close();
        } catch (Exception excp) {
            excp.printStackTrace();
        }
        return toReturn;
    }

    public static void getMysqlDumpZip() {
        JFileChooser jfc = new JFileChooser();
        jfc.setDialogTitle(Localisation.getMessage("form.selectplaceandname"));
        jfc.showSaveDialog(null);
        jfc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);

        final String dirPath = jfc.getCurrentDirectory().getAbsolutePath() + "\\".replace('\\', '/');
//                 dirPath = dirPath.replace('\\', '/');
        final String fileName = returnFormattedDataFromDate(new java.util.Date()) + "_" + jfc.getSelectedFile().getName() + "_fs1.sql";
        File file = new File(dirPath + fileName);
        try {
            Thread runDump = new Thread(ThreadFactory.runMysqlDump(file));
            runDump.start();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static List<List<String>> getListFieldsTranslate(String tableName) {
        List<List<String>> fieldTranslateList = new ArrayList<List<String>>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement(StoredQueries.getQuery(StoredQueries.FIELDS_TRANSLATE));
            ps.setString(1, tableName);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                List<String> tempList = new ArrayList<String>();
                tempList.add(rs.getString("analys_param"));
                tempList.add(rs.getString(SGTTestParametrs.getInstance().getCurrentLocal().getColumn()));
                fieldTranslateList.add(tempList);
            }
        } catch (Exception exp) {
            exp.printStackTrace();
        }
        return fieldTranslateList;
    }

    ;

    public static String returnTranslateField(String tableName, String fieldName) {
        List<List<String>> fieldTranslateList = StaticMethods.getListFieldsTranslate(tableName);
        for (int k = 0; k < fieldTranslateList.size(); k++) {
            if (fieldName.equals(fieldTranslateList.get(k).get(0))) {
                return fieldTranslateList.get(k).get(1);
            }
        }
        return "";
    }

    ;

    public static Frame getFrameByName(String frameName) {
        /*
        This method return Frame by name
         */
        Frame[] frames = JFrame.getFrames();
        Frame frame = null;
        for (int i = 0; i < frames.length; i++) {
            if (frames[i].getName().equals(frameName)) {
                frame = frames[i];
//                break;
            }
        }
        return frame;

    }

    public static JFrame getProgressFrame(String progressFrameName, String progressBarString) {
        JFrame progressFrame = new JFrame();
        JPanel progressPanel = new JPanel();
        JProgressBar progressBar = new JProgressBar();
        progressBar.setIndeterminate(true);
        progressBar.setStringPainted(true);
        progressBar.setString(progressBarString);
        progressPanel.add(progressBar);
        progressFrame.add(progressPanel);
        progressFrame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        progressFrame.setName(progressFrameName);
        progressFrame.setSize(350, 100);
        progressFrame.setLocation(300, 200);
        progressFrame.setEnabled(false);
        progressFrame.setVisible(true);
        return progressFrame;
    }

    public static void createZip(String pathOutputZip, File inputFile) {
        // Create a buffer for reading the files
        byte[] buf = new byte[1024];
        FileInputStream in = null;
        try {
            in = new FileInputStream(inputFile.getAbsolutePath());
            FileOutputStream outFile = new FileOutputStream(pathOutputZip);
            ZipOutputStream zip = new ZipOutputStream(new BufferedOutputStream(outFile));
            zip.setMethod(ZipOutputStream.DEFLATED);
            zip.setLevel(Deflater.BEST_COMPRESSION);
            zip.putNextEntry(new ZipEntry(inputFile.getName()));
            // Transfer bytes from the file to the ZIP file
            int len;
            while ((len = in.read(buf)) > 0) {
                zip.write(buf, 0, len);
            }
            zip.close();
            outFile.close();
            in.close();
        } catch (FileNotFoundException e) {
            System.out.println("file not found");
            e.printStackTrace();
        } catch (IOException e) {
            System.out.println("IO error");
            e.printStackTrace();
        } finally {
            try {
                if (in != null) {
                    in.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    public static List<GroupsSchemaVisitsId> getGroupsSchemaVisitsIdList() {
        List<GroupsSchemaVisitsId> toReturn = new ArrayList<GroupsSchemaVisitsId>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("select * from groups_schema_visits");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                GroupsSchemaVisitsId gsvi = new GroupsSchemaVisitsId();
                gsvi.setGroupsSchemaVisitsId(rs.getInt("groups_schema_visits_id"));
                gsvi.setSchemaVisitsId(rs.getInt("schema_visits_id"));
                gsvi.setDicTestTypeId(rs.getString("dic_test_type_id"));
                gsvi.setSchemaVisitsName();
                toReturn.add(gsvi);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return toReturn;
    }

    public static Map<List<String>, List<DicGeneral>> getMapFromDic() {
        /*
        This method return Map from dic_general
         */
        Map<List<String>, List<DicGeneral>> mapDicGeneral = new HashMap<List<String>, List<DicGeneral>>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM dic_general");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                List<String> keyList = new ArrayList<String>(3);
                List<DicGeneral> valueList = new ArrayList<DicGeneral>();
                keyList.add(rs.getString("table_name"));
                keyList.add(rs.getString("table_field"));
                keyList.add(rs.getString("dic_type"));

                DicGeneral dicGeneral = new DicGeneral();
                dicGeneral.setDicId(rs.getInt("dic_id"));
                dicGeneral.setNameRu(rs.getString("name_ru"));
                dicGeneral.setNameEn(rs.getString("name_en"));
                dicGeneral.setNameKz(rs.getString("name_kk"));
                dicGeneral.setList_subfields(rs.getString("list_subfields"));
                valueList.add(dicGeneral);
                if (mapDicGeneral.get(keyList) == null) {
                    mapDicGeneral.put(keyList, valueList);
                } else {
                    List<DicGeneral> valueTempList = mapDicGeneral.get(keyList);
                    valueTempList.add(dicGeneral);
                    mapDicGeneral.put(keyList, valueTempList);
                }

            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return mapDicGeneral;
    }

    public static List<String> getUnitList() {
        List<String> unitsList = new ArrayList<String>();
        try {
            Connection conn = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM dic_units WHERE unit NOT LIKE 'DIC_%'");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                unitsList.add(rs.getString(SGTTestParametrs.getOurInstance().getCurrentLocal().getColumn()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return unitsList;
    }

    public static JPanel getCalendarPanel(String panelAndDataFieldName) {
        JPanel datePanel = new JPanel();
        datePanel.setName(panelAndDataFieldName);
        JFormattedTextField dateField = new JFormattedTextField(new SimpleDateFormat("dd-MM-yyyy"));
        dateField.setName(panelAndDataFieldName);
        dateField.setColumns(8);
        dateField.setEditable(false);
        String path = "images/Calendar-icon.png";
        URL imgURL = ComponentsToPanel.class.getResource(path);
        ImageIcon icon = new ImageIcon(imgURL);
        JButton calendarButton = new JButton(icon);
        calendarButton.setPreferredSize(
                new Dimension(icon.getIconWidth(),
                        icon.getIconHeight()));
        calendarButton.setToolTipText(Localisation.getMessage("tip.clickme"));
        calendarButton.addActionListener(ListenerFactory.getFrameCalendar(dateField));
        datePanel.add(dateField, BorderLayout.WEST);
        datePanel.add(calendarButton, BorderLayout.EAST);
        return datePanel;
    }

    public static JFormattedTextField getJFormattedTextFieldOfCalendarPanel(JPanel calendarPanel) {
        JFormattedTextField jtf = (JFormattedTextField) calendarPanel.getComponents()[0];
        return jtf;
    }

    public static void selectAllIndicesInJList(JList jList) {
        /*
        This method setSelectedIndices to all elements in JList
         */
        ListModel listModel = jList.getModel();
        int size = listModel.getSize();
        int[] arrayIndices = new int[size];
        for (int i = 0; i < listModel.getSize(); i++) {
            arrayIndices[i] = i;
        }
        jList.setSelectedIndices(arrayIndices);
    }

    public static void changeDefaultComboBoxModel(JComboBox jComboBox, Object[] array) {
        jComboBox.removeAllItems();
        DefaultComboBoxModel defaultComboBoxModel = new DefaultComboBoxModel(array);
        jComboBox.setModel(defaultComboBoxModel);
    }

    public static LinkedList<DbTable> getLinkedListTable(String dataBaseName) {
        LinkedList<DbTable> linkedListTable = new LinkedList<DbTable>();
        try {
            Connection connection = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = connection.prepareStatement("select * from " +
                    dataBaseName + ".dic_analysis_instr_invest_list order by analysis_instr_invest_list_id");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DbTable dbTable = new DbTable(dataBaseName, rs.getString("analysis_name"), null);
                dbTable.setTestTypeId(rs.getString("test_type_id"));
                dbTable.setNameRu(rs.getString("name_ru"));
                dbTable.setNameEn(rs.getString("name_en"));
                dbTable.setNameKz(rs.getString("name_kk"));
                dbTable.setColumnNamesLinkedList(StaticMethods.getColumnNamesOfTable(dataBaseName, dbTable.getTableName()));
                linkedListTable.add(dbTable);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return linkedListTable;
    }

    public static Map<String, DbTable> getMapTable(String dataBaseName) {
        /*
       This method return map of test tables
       */
        Map<String, DbTable> mapTable = new HashMap<String, DbTable>();
        try {
            Connection connection = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = connection.prepareStatement("select * from " +
                    dataBaseName + ".dic_analysis_instr_invest_list order by analysis_instr_invest_list_id");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DbTable dbTable = new DbTable(dataBaseName, rs.getString("analysis_name"), null);
                dbTable.setTestTypeId(rs.getString("test_type_id"));
                dbTable.setNameRu(rs.getString("name_ru"));
                dbTable.setNameEn(rs.getString("name_en"));
                dbTable.setNameKz(rs.getString("name_kk"));
                dbTable.setColumnNamesLinkedList(StaticMethods.getColumnNamesOfTable(dataBaseName, dbTable.getTableName()));
                dbTable.setMapTableFields(StaticMethods.getFieldMapOfTable(dataBaseName, dbTable.getTableName()));
                mapTable.put(rs.getString("analysis_name"), dbTable);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return mapTable;
    }

    public static Map<String, DbTable> getMapSpecifiedTables(String dataBaseName, String tablesName) {
        /*
       This method return map of specified test tables
       */
        Map<String, DbTable> mapTable = new HashMap<String, DbTable>();
        try {
            Connection connection = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = connection.prepareStatement("select * from " +
                    dataBaseName + ".dic_analysis_instr_invest_list" +
                    " where analysis_name IN(" + tablesName + ")" +
                    " order by analysis_instr_invest_list_id");
//            System.out.println(ps.toString());
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                DbTable dbTable = new DbTable(dataBaseName, rs.getString("analysis_name"), null);
                dbTable.setTestTypeId(rs.getString("test_type_id"));
                dbTable.setNameRu(rs.getString("name_ru"));
                dbTable.setNameEn(rs.getString("name_en"));
                dbTable.setNameKz(rs.getString("name_kk"));
                dbTable.setColumnNamesLinkedList(StaticMethods.getColumnNamesOfTable(dataBaseName, dbTable.getTableName()));
                dbTable.setMapTableFields(StaticMethods.getFieldMapOfTable(dataBaseName, dbTable.getTableName()));
                mapTable.put(rs.getString("analysis_name"), dbTable);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }

        return mapTable;
    }

    /*
    public static LinkedList<String> getLinkedListQueries(String dataBaseName) {
        LinkedList<String> linkedListQueries = new LinkedList<String>();
        try {
            for (DbTable dbTable : StaticMethods.getLinkedListTable(dataBaseName)) {
                String query = "select " + dbTable.getDataBaseName() + "." + dbTable.getTableName() + ".* from " + dbTable.getDataBaseName() + "." + dbTable.getTableName() +
                        " inner join " + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() +
                        " on(" + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() + "." + "test_id=" + dbTable.getDataBaseName() + "." + dbTable.getTableName() + ".test_id)";
                for (int i = 0; i < dbTable.getColumnNamesLinkedList().size(); i++) {
                    String columnName = dbTable.getColumnNamesLinkedList().get(i);
                    if (i == 0) {
                        query = query +
                                " where " + dbTable.getDataBaseName() + "." + dbTable.getTableName() + "." + columnName +
                                "!=" + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() + "." + columnName;
                    } else {
                        query = query +
                                " or " + dbTable.getDataBaseName() + "." + dbTable.getTableName() + "." + columnName +
                                "!=" + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() + "." + columnName;
                    }

                }
                linkedListQueries.add(query);
//                System.out.println("query: " + query);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
//        System.out.println("linkedListQueries.size: " + linkedListQueries.size());
        return linkedListQueries;
    }
    */
    public static LinkedList<String> getLinkedListQueriesFromMap(String dataBaseName) {
        LinkedList<String> linkedListQueries = new LinkedList<String>();
        try {
            for (DbTable dbTable : RunDBChecking.mapTable.values()) {
                String query = "select " + dbTable.getDataBaseName() + "." + dbTable.getTableName() + ".* from " + dbTable.getDataBaseName() + "." + dbTable.getTableName() +
                        " inner join " + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() +
                        " on(" + dbTable.getDataBaseName() + "_copy." + dbTable.getTableName() + "." + "test_id=" + dbTable.getDataBaseName() + "." + dbTable.getTableName() + ".test_id)";
                for (int i = 0; i < dbTable.getColumnNamesLinkedList().size(); i++) {
                    String columnName = dbTable.getColumnNamesLinkedList().get(i);
                    if (i == 0) {
                        query = query +
                                " where (" + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                                "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
                    } else {
                        query = query +
                                " or " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName +
                                "!=" + DbUtils.SECONDARY_DATABASE_NAME + "." + dbTable.getTableName() + "." + columnName;
                    }

                }
                query = query +
                        ") and " + DbUtils.GENERAL_DATABASE_NAME + "." + dbTable.getTableName() + ".test_id in(" +
                        " SELECT test_id " +
                        " FROM visit_details " +
                        " WHERE probationer_id IN(SELECT probationer_id FROM probationers " +
                        " INNER JOIN groups ON (probationers.group_id=groups.group_id) " +
                        " INNER JOIN dic_phase ON (groups.phase_id = dic_phase.phase_id)" +
                        " WHERE dic_phase.phase_id IN(" + StaticMethods.joinPhaseListToString(RunDBChecking.phaseToReturn) + ")) " +
                        ") order by test_id";
                linkedListQueries.add(query);
//                System.out.println("query: " + query);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
//        System.out.println("linkedListQueries.size: " + linkedListQueries.size());
        return linkedListQueries;
    }

    public static List<List<String>> getDataForCheck(LinkedList<String> linkedListQueries) {
        List<List<String>> dataList = new ArrayList<List<String>>();
        Connection conn;
//        PreparedStatement ps1 = null;
//        PreparedStatement ps2 = null;
        for (String query : linkedListQueries) {
            try {
                conn = DbUtils.getDBConnectionGeneral();
                PreparedStatement ps1 = conn.prepareStatement(query);
                PreparedStatement ps2 = conn.prepareStatement(query.replace("select fs1.", "select fs1_copy.").
                        replace(" and " + DbUtils.GENERAL_DATABASE_NAME, " and " + DbUtils.SECONDARY_DATABASE_NAME));
//                System.out.println("ps2 :"+ps2.toString());
                PreparedStatement psGetProbationer = conn.prepareStatement("SELECT probationers.probationer_code,\n" +
                        "schema_visit_time.time_mls/(24*3600) AS days\n" +
                        "FROM visit_details\n" +
                        "INNER JOIN probationers ON(probationers.probationer_id=visit_details.probationer_id)\n" +
                        "INNER JOIN schema_visit_time ON(schema_visit_time.visit_id=visit_details.visit_id)\n" +
                        "WHERE\n" +
                        "test_id=?\n" +
                        "AND schema_visits_id IN(\n" +
                        "SELECT\n" +
                        "groups_schema_visits.schema_visits_id\n" +
                        "FROM\n" +
                        "probationers\n" +
                        "INNER JOIN groups ON (probationers.group_id = groups.group_id)\n" +
                        "INNER JOIN groups_test_type ON (groups.group_id = groups_test_type.group_id)\n" +
                        "INNER JOIN groups_schema_visits ON (groups_test_type.groups_schema_visits_id = groups_schema_visits.groups_schema_visits_id)\n" +
                        "WHERE\n" +
                        "probationer_id=(SELECT probationer_id FROM visit_details WHERE test_id=?)\n" +
                        "AND groups_schema_visits.dic_test_type_id=?\n" +
                        ");");
                ResultSet rs1 = ps1.executeQuery();
                ResultSet rs2 = ps2.executeQuery();
                ResultSet rsGetProbationer = null;
                while (rs1.next()) {
                    ResultSetMetaData rsmd1 = rs1.getMetaData();
                    while (rs2.next()) {
                        if (rs1.getInt("test_id") == rs2.getInt("test_id")) {
                            for (int i = 1; i < rsmd1.getColumnCount() + 1; i++) {
                                if (rsmd1.getColumnName(i).equals("user")
                                        || rsmd1.getColumnName(i).equals("insert_datetime")
                                        || rsmd1.getColumnName(i).equals("modified_date")
                                        || rsmd1.getColumnName(i).equals("modified_user")
                                        || rsmd1.getColumnName(i).equals("exceed_max_min")
                                        || rsmd1.getColumnName(i).equals("version_id")) {
                                    continue;
                                } else {
                                    if (!rs1.getString(i).equals(rs2.getString(i))) {
                                        psGetProbationer.setString(1, rs1.getString("test_id"));
                                        psGetProbationer.setString(2, rs1.getString("test_id"));
                                        psGetProbationer.setString(3, RunDBChecking.mapTable.get(rsmd1.getTableName(i)).getTestTypeId());
                                        rsGetProbationer = psGetProbationer.executeQuery();
                                        List<String> tempList = new ArrayList<String>();
                                        tempList.add(RunDBChecking.mapTable.get(rsmd1.getTableName(i)).getTableName());
                                        tempList.add(RunDBChecking.mapTable.get(rsmd1.getTableName(i)).toString());
                                        tempList.add(StaticMethods.returnTranslateField(rsmd1.getTableName(i), rsmd1.getColumnName(i)));
                                        String unit = RunDBChecking.mapTable.get(rsmd1.getTableName(i)).getMapTableFields().get(rsmd1.getColumnName(i)).getUnit();
                                        List<String> keyList = new ArrayList<String>();
                                        keyList.add(rsmd1.getTableName(i));
                                        keyList.add(rsmd1.getColumnName(i));
                                        keyList.add(unit.replaceAll("REFERENCE_", "").toLowerCase());

                                        if (VolunteerSelectorView.getMapDicGeneral().get(keyList) != null) {
                                            for (DicGeneral dicGeneral : VolunteerSelectorView.getMapDicGeneral().get(keyList)) {
                                                if (dicGeneral.getDicId() == rs1.getInt(i)) {
                                                    tempList.add(dicGeneral.toString());
                                                    for (DicGeneral dicGeneral2 : VolunteerSelectorView.getMapDicGeneral().get(keyList)) {
                                                        if (dicGeneral2.getDicId() == rs2.getInt(i)) {
                                                            tempList.add(dicGeneral2.toString());
                                                        }
                                                    }
                                                }

                                            }
                                        } else {
                                            tempList.add(rs1.getString(i));
                                            tempList.add(rs2.getString(i));
                                        }

                                        while (rsGetProbationer.next()) {
                                            tempList.add(rsGetProbationer.getString("probationer_code"));
                                            tempList.add(String.valueOf(Math.round(Double.parseDouble(rsGetProbationer.getString("days")))));
                                        }

                                        tempList.add(rs1.getString("test_id"));
                                        dataList.add(tempList);
//                                        System.out.println("tableName:" + rsmd1.getTableName(i) +
//                                                " test_id_Original: " + rs1.getString("test_id") +
//                                                " test_id_Copy: " + rs2.getString("test_id") +
//                                                " columnName: " + rsmd1.getColumnName(i) + " columnValueOriginal: " + rs1.getString(i) +
//                                                " columnValueCopy: " + rs2.getString(i));
                                    }
                                }

                            }
                            break;
                        }
                    }
                }
                rs1.close();
                rs2.close();
                ps1.close();
                ps2.close();
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return dataList;
    }

    public static Integer getMaxValueOfAutoIncrementField(String tableName, String fieldAutoIncrement) {
        Integer maxFieldId = null;
        Integer maxFieldIdFromGeneral = null;
        Integer maxFieldIdFromSecondary = null;
        String query = "select max(" + fieldAutoIncrement + ") as " + fieldAutoIncrement + " from " + tableName;
        try {
            Connection connGeneral = DbUtils.getDBConnectionGeneral();
            PreparedStatement psGeneral = connGeneral.prepareStatement(query);
            ResultSet rsGeneral = psGeneral.executeQuery();
            while (rsGeneral.next()) {
                maxFieldIdFromGeneral = rsGeneral.getInt(fieldAutoIncrement);
            }

            Connection connSecondary = DbUtils.getDBConnectionSecondary();
            PreparedStatement psSecondary = connSecondary.prepareStatement(query);
            ResultSet rsSecondary = psSecondary.executeQuery();
            while (rsSecondary.next()) {
                maxFieldIdFromSecondary = rsSecondary.getInt(fieldAutoIncrement);
            }
            if (maxFieldIdFromGeneral > maxFieldIdFromSecondary) {
                maxFieldId = maxFieldIdFromGeneral + 1;
            } else {
                maxFieldId = maxFieldIdFromSecondary + 1;
            }

            connGeneral.close();
            connSecondary.close();

        } catch (Exception e) {
            e.printStackTrace();
        }
        return (maxFieldId==0 ? 1 : maxFieldId);
    }

    public static Integer getResultAnswerId(int groupId, int probationerId, int visitId, int questionId) {
        Integer toReturn = null;
        try {
            Connection connGeneral = DbUtils.getDBConnectionGeneral();
            PreparedStatement psGeneral = null;
            Connection connSecondary = null;
            PreparedStatement psSecondary = null;
            String query = "SELECT " +
                    " result_answers.result_answer_id," +
                    " questions.question_id " +
                    " FROM\n" +
                    " result_answers\n" +
                    " INNER JOIN answers ON (result_answers.answer_id = answers.answer_id)\n" +
                    " INNER JOIN questions ON (answers.question_id = questions.question_id)\n" +
                    " WHERE " +
                    " group_id=?" +
                    " and probationer_id=?" +
                    " and vizit_id=?" +
                    " and questions.question_id=?";
            psGeneral = connGeneral.prepareStatement(query);
            psGeneral.setInt(1, groupId);
            psGeneral.setInt(2, probationerId);
            psGeneral.setInt(3, visitId);
            psGeneral.setInt(4, questionId);
//            System.out.println(psGeneral.toString());
            ResultSet rsGeneral = psGeneral.executeQuery();
            while (rsGeneral.next()) {
                toReturn = rsGeneral.getInt("result_answer_id");
            }
            connGeneral.close();
            if (toReturn == null & DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                connSecondary = DbUtils.getDBConnectionSecondary();
                psSecondary = connSecondary.prepareStatement(query);
                psSecondary.setInt(1, groupId);
                psSecondary.setInt(2, probationerId);
                psSecondary.setInt(3, visitId);
                psSecondary.setInt(4, questionId);
                ResultSet rsSecondary = psSecondary.executeQuery();
                while (rsSecondary.next()) {
                    toReturn = rsSecondary.getInt("result_answer_id");
                }
                connSecondary.close();
                if (toReturn == null) {
                    toReturn = StaticMethods.getMaxValueOfAutoIncrementField("result_answers", "result_answer_id");
                }
            }


        } catch (Exception e) {
            e.printStackTrace();
        }

        return toReturn;
    }

    public static void saveValuesToResultAnswers(Probationer probationer, int visitId, Map<Integer, Answer> mapQuestionsAnswers, String operation) {
       /*
       This method make insert and update for questionnaire SF-36
        */
        try {
            Connection connGeneral = DbUtils.getDBConnectionGeneral();
            PreparedStatement psGeneral=null;
            int i = 0;
            JFormattedTextField dateField = new JFormattedTextField(new SimpleDateFormat("dd-MM-yyyy"));
            dateField.setValue(new Date());
            StaticMethods.returnFormatedData(dateField.getText());
            for (Map.Entry<Integer, Answer> qam : mapQuestionsAnswers.entrySet()) {
                Integer result_answer_id = StaticMethods.getResultAnswerId(probationer.getGroup_id(), probationer.getProbationer_id(), visitId, qam.getKey());
//                            System.out.println("result_answer_id : "+result_answer_id);
                String sqlQuery="";
                if(operation.equals("new")){
                 sqlQuery = "insert into result_answers" +
                        "(result_answer_id,group_id," +
                        "probationer_id,date_of_answer,vizit_id," +
                        "answer_id,points,user,insert_datetime)" +
                        " values(" + result_answer_id + "," + probationer.getGroup_id() + ","
                        + probationer.getProbationer_id() + ",'0000-00-00'," +
                        visitId + "," + qam.getValue().getAnswerId() + ","
                        + qam.getValue().getPoints() + ","
                        + "'" + SGTTestParametrs.getInstance().getUserName() + "',"
                        + "'" + StaticMethods.returnFormatedData(dateField.getText()) + "');";
                }else {
                  sqlQuery="update result_answers set answer_id="+qam.getValue().getAnswerId()+","+
                          " points="+qam.getValue().getPoints()+","+
                          " modified_date='" +StaticMethods.returnFormatedData(dateField.getText())+"',"+
                          " modified_user="+ "'" + SGTTestParametrs.getInstance().getUserName() + "'"+
                          " where result_answer_id="+result_answer_id;

                }

                psGeneral = connGeneral.prepareStatement(sqlQuery);
//                            System.out.println(psGeneral.toString());
                if (psGeneral.executeUpdate() > 0) {
                    i++;
                }
                if (i == 36) {
                    JOptionPane.showMessageDialog(null, Localisation.getMessage("info.dataadded"), "", JOptionPane.INFORMATION_MESSAGE);
                    StaticMethods.getFrameByName(Localisation.getMessage("menu.questionnaire") + " " + DbUtils.ModeOfOperation).dispose();
                }
            }
            psGeneral.close();
            connGeneral.close();
        } catch (Exception excep) {
            JOptionPane.showMessageDialog(null, Localisation.getMessage("error.applicationerror"), "error",
                    JOptionPane.ERROR_MESSAGE);
            excep.printStackTrace();
        }
    }
    
    public static String getTestTime(int groups_schema_visits_id, int visit_id){
        String testTime="";
        try{
            Connection connGeneral = DbUtils.getDBConnectionGeneral();
            PreparedStatement psGeneral = connGeneral.prepareStatement(StringQueries.TEST_TIME);
            psGeneral.setInt(1, groups_schema_visits_id);
            psGeneral.setInt(2, visit_id);
            ResultSet rsGeneral = psGeneral.executeQuery();
            while (rsGeneral.next()){
                testTime=rsGeneral.getString("testTime");
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return testTime;


    }

    public static Integer getFileId(String fileName) {
        Integer toReturn = null;
        try {
            Connection connGeneral = DbUtils.getDBConnectionGeneral();
            PreparedStatement psGeneral = null;
            Connection connSecondary = null;
            PreparedStatement psSecondary = null;
            String query = "SELECT file_id FROM files WHERE file_name=?";
            psGeneral = connGeneral.prepareStatement(query);
            psGeneral.setString(1, fileName);
//            System.out.println(psGeneral.toString());
            ResultSet rsGeneral = psGeneral.executeQuery();
            while (rsGeneral.next()) {
                toReturn = rsGeneral.getInt("file_id");
            }
            connGeneral.close();
            if (toReturn == null & DbUtils.mapDataBases.get(DbUtils.SECONDARY_DATABASE_NAME) != null) {
                connSecondary = DbUtils.getDBConnectionSecondary();
                psSecondary = connSecondary.prepareStatement(query);
                psSecondary.setString(1, fileName);
                ResultSet rsSecondary = psSecondary.executeQuery();
                while (rsSecondary.next()) {
                    toReturn = rsSecondary.getInt("file_id");
                }
                connSecondary.close();
                if (toReturn == null) {
                    toReturn = StaticMethods.getMaxValueOfAutoIncrementField("files", "file_id");
                }
            }


        } catch (Exception e) {
            e.printStackTrace();
        }

        return toReturn;
    }
    
    public static void changeInSystem(int inSystem, String computerName,String login) throws SQLException {
        Connection connGeneral = null;
        PreparedStatement psGeneral = null;
        Connection connSecondary = null;
        PreparedStatement psSecondary = null;

       try{
           connGeneral=DbUtils.getDBConnectionGeneral();
           psGeneral=connGeneral.prepareStatement("UPDATE login SET in_system=?,computer_name=? WHERE login=?");
           psGeneral.setInt(1, inSystem);
           psGeneral.setString(2, computerName);
           psGeneral.setString(3, login);

           connSecondary=DbUtils.getDBConnectionSecondary();
           psSecondary=connSecondary.prepareStatement("UPDATE login SET in_system=?,computer_name=? WHERE login=?");
           psSecondary.setInt(1, inSystem);
           psSecondary.setString(2, computerName);
           psSecondary.setString(3, login);

           if(psGeneral.executeUpdate()>0){
               psSecondary.executeUpdate();
           }
       }catch (Exception e){
           e.printStackTrace();
       }
        finally {
           connGeneral.close();
           psGeneral.close();
           connSecondary.close();
           psSecondary.close();
       }
    }

    public static void changeAmountAccessFailures(int amountAccessFailures, String login) throws SQLException {
        Connection connGeneral = null;
        PreparedStatement psGeneral = null;
        Connection connSecondary = null;
        PreparedStatement psSecondary = null;

        try{
            connGeneral=DbUtils.getDBConnectionGeneral();
            psGeneral=connGeneral.prepareStatement("UPDATE login SET amount_access_failures=? WHERE login=?");
            psGeneral.setInt(1, amountAccessFailures);
            psGeneral.setString(2, login);

            connSecondary=DbUtils.getDBConnectionSecondary();
            psSecondary=connSecondary.prepareStatement("UPDATE login SET amount_access_failures=? WHERE login=?");
            psSecondary.setInt(1, amountAccessFailures);
            psSecondary.setString(2, login);

            if(psGeneral.executeUpdate()>0){
                psSecondary.executeUpdate();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        finally {
            connGeneral.close();
            psGeneral.close();
            connSecondary.close();
            psSecondary.close();
        }
    }

}
